<?php
/**
 * ExcelHandler     [Excel封装库，支持读和简单的写功能]
 * Tips: 该库是基于PHPExcel的二次封装，请不要移动Classes目录
 * Time: 2016-04-11
 * @author dt <804413587@qq.com>
 * @version 1.0
 */
namespace app\utils\ExcelHandler;

use PHPExcel_Reader_Exception;
use yii\web\UploadedFile;

class ExcelHandler
{
	//excel 实例
	private static $instance = null;
    //当前需要读取的Excel文件路径
	private static $file = '';
    //根据后缀选取相应的处理类
	private static $handler = '';
    //读取Excel
	private static $excel = '';
    //当前工作表
	private static $currentSheet = '';
    //当前Excel的所有工作表信息
	private static $sheetsInfo = [];
    //当前Excel的工作表总数
	private static $sheetsCount = 0;
    //获取当前工作表的行和列
	private static $sheetSize = [];

    /**
     * ExcelHandler 构造方法.
     */
    private function __construct()
    {

	}

    /**
     * 单例实例化方法
     * @return ExcelHandler|null excel实例
     */
    public static function getInstance()
    {
		if (empty(self::$instance)) {
			self::$instance = new self();
		}
		return self::$instance;
	}

    /**
     * 加载某个excel文件
     * @param string $fileName 目标excel文件
     * @param int $sheet 工作表索引，默认加载第一张表
     * @param string $InputEncoding csv文件编码，若出现不能读取中文或者中文乱码，切换GBK和UTF-8试试，默认认为是GBK
     * @return ExcelHandler|null excel实例
     */
    public function load($fileName = '', $sheet = 0, $InputEncoding = 'GBK')
    {
        $file = UploadedFile::getInstanceByName($fileName);
		if (!file_exists($file->tempName)) {
			self::_throwException("文件不存在");
		}
		if (!self::excelFileValidate($file)) {
            self::_throwException("文件类型有误");
        }
		self::$file = $file->tempName;
		//加载依赖库
		require_once(dirname(__FILE__) . '/Classes/PHPExcel.php');
		try {
			self::$handler = \PHPExcel_IOFactory::createReaderForFile(self::$file);
			self::_csvEncoding($InputEncoding);
			self::_sheetsInfo();
			self::$sheetsCount = count(self::$sheetsInfo);
			self::loadSheet($sheet);
			return self::$instance;
		} catch (PHPExcel_Reader_Exception $e) {
			self::_throwException($e->getMessage());
		}
	}

    /**
     * 加载工作表
     * @param int $sheet 工作表索引
     * @return ExcelHandler|null excel实例
     */
    public function loadSheet($sheet = 0)
    {
		self::_checkLoad();
		if ($sheet >= self::$sheetsCount) {
			self::_throwException("工作表索引错误，最大值为：" . (self::$sheetsCount-1));
		}
		try {
			//读取该Excel文件
			self::$excel = self::$handler->load(self::$file);
			//加载工作表
			self::$currentSheet = self::$excel->getSheet($sheet);
			self::_sheetSize();
			return self::$instance;
		} catch (PHPExcel_Reader_Exception $e) {
			self::_throwException($e->getMessage());
		}	
	}

    /**
     * 获取当前工作表行和列尺寸
     * @return array excel行和列
     */
    public function getSheetSize()
    {
		self::_checkLoad();
		return self::$sheetSize;
	}

    /**
     * 获取所有工作表信息
     * @return array 工作表信息
     */
    public function getSheetsInfo()
    {
		self::_checkLoad();
		return self::$sheetsInfo;
	}

    /**
     * 获取工作表总数
     * @return int 工作表总数
     */
    public function getSheetsCount()
    {
		self::_checkLoad();
		return self::$sheetsCount;
	}

    /**
     * 获取合并的单元格信息
     * @return mixed
     */
    public function getMergeCells()
    {
		self::_checkLoad();
		return self::$currentSheet->getMergeCells();
	}

    /**
     * 读取行
     * @param $rowIndex 行索引 \d
     * @return array 行索引
     */
    public function readRow($rowIndex)
    {
		$row = [];
		$rows = self::read('A', $rowIndex, '', $rowIndex);
		if (!empty($rows)) {
			$row["row_{$rowIndex}"] = $rows[0];
		}
		return $row;
	}

    /**
     * 读取列
     * @param $columnIndex 列索引 [A-Za-z]
     * @return array
     */
    public function readColumn($columnIndex)
    {
		$colum = [];
		$columns = self::read($columnIndex, 1, $columnIndex);
		if (!empty($columns)) {
			foreach ($columns as $k => $v) {
				$colum["column_" . strtolower($columnIndex)][$k] = $v[0];
			}
		}
		return $colum;
	}

    /**
     * 读取
     * @param string $colStart  起始列标，[A-Za-z]，为空从A开始
     * @param int $rowStart     起始行标，\d，为空从1开始
     * @param string $colEnd    结束列标，[A-Za-z]，为空到sheetSize()检测的最大列标结束
     * @param string $rowEnd    结束行标，\d，为空到sheetSize()检测的最大行标结束
     * @return array    返回以行为第一维的二维数组
     */
    public function read($colStart = 'A', $rowStart = 1, $colEnd = '', $rowEnd = '')
    {
		self::_checkLoad();
		$cs = strtoupper($colStart);
		$rs = $rowStart;
		$ce = !empty($colEnd) ? strtoupper($colEnd) : self::$sheetSize['column'];
		$re = !empty($rowEnd) ? $rowEnd : self::$sheetSize['row'];
		//参数过滤
		if (!preg_match('/[A-Z]+/', $cs) || !preg_match('/[A-Z]+/', $ce) || !preg_match('/\d+/', $rs) || !preg_match('/\d+/', $re)) {
			self::_throwException("参数错误，参数1,3[A-Za-z]、2,4[\d]或者留空");
		}
		if (self::_col2num($cs) > PHP_INT_MAX || self::_col2num($ce) > PHP_INT_MAX) {	//超过整型范围不支持，实际应用也没那么宽的列
			self::_throwException("参数错误，参数超过范围");
		}
		//二维计算
		$cols = self::_col2num($ce) - self::_col2num($cs)+1;
		$rows = $re-$rs+1;
		//获取数据
		$data = [];
		for ($i=0; $i<$rows; $i++) {	//行
			$row = $rs+$i;
			for ($j=0; $j<$cols; $j++) {	//列
				$col = self::_num2col(self::_col2num($cs)+$j);
				$position = $col . $row;
				$data[$i][$j] = self::$currentSheet->getCell($position)->getValue();
			}
		}
		return $data;
	}

    /**
     *写入
     */
    public function write()
    {
	
	}

    /**
     * @param array $data       一个二维数组,结构如同从数据库查出来的数组
     * @param array $title      excel的第一行标题,一个数组,如果为空则没有标题
     * @param string $filename  下载的文件名
     * @param string $ext       后缀
     * @examlpe
     *      $stu = M ('User');
     *      $data = $stu -> select();
     *      ExcelHandler::download($data, array('id','账户','密码','昵称'), '文件名');
     * @tips	导出文件的页面不允许有任何输出！！！
     */
    public static function export($data = [], $title = [], $filename = 'export', $ext = 'xls')
    {
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=" . $filename . ".{$ext}");
        header("Pragma: no-cache");
        header("Expires: 0");
        if (!empty($title)) {
            foreach ($title as $k => $v) {
                $title[$k] = iconv("UTF-8", "GB2312", $v);
            }
            $title = implode("\t", $title);
            echo "$title\n";
        }
        if (!empty($data)) {
            foreach ($data as $key => $val) {
                foreach ($val as $ck => $cv) {
                    $data[$key][$ck] = iconv("UTF-8", "GB2312", $cv);
                }
                $data[$key] = implode("\t", $data[$key]);
            }
            echo implode("\n", $data);
        }
	}

    /**
     * CSV后缀编码转换
     * @param $inputEncode
     */
    private function _csvEncoding($inputEncode)
    {
		$pathInfo = pathinfo(self::$file);
		if (strtolower($pathInfo['extension']) == 'csv') {
			self::$handler->setInputEncoding($inputEncode);
		}
	}

    /**
     * 当前工作表行和列尺寸
     */
    private function _sheetSize()
    {
		try {
			self::$sheetSize = self::$currentSheet->getHighestRowAndColumn();
		} catch (PHPExcel_Reader_Exception $e) {
			self::_throwException($e->getMessage());
		}
	}

    /**
     * 所有工作表信息
     */
    private function _sheetsInfo()
    {
		try {
			self::$sheetsInfo = self::$handler->listWorksheetInfo(self::$file);
		} catch(PHPExcel_Reader_Exception $e) {
			self::_throwException($e->getMessage());
		}
	}

    /**
     * 检测是否已经加载Excel
     */
    private function _checkLoad()
    {
		if (empty(self::$handler)) {	//handler还未加载
			self::_throwException("请先调用 load()");
		}
	}
    /**
     * 数字转列标
     * @param $num      \d
     * @return string   10进制数对应的列标，[A-Za-z]
     */
    private function _num2col($num)
    {
		$col = '';
		do {
			$y = intval(fmod(floatval($num), 26));
			$num = intval($num / 26);
			if ($y == 0) {
				$y = 26;
				$num -= 1;
			}
			$col = chr(64 + $y) . $col;
		} while ($num > 0);
		return $col;
	}

    /**
     * 列标转数字
     * @param $col  [A-Za-z]
     * @return int  列标对应的10进制整数，\d
     */
    private function _col2num($col)
    {
		$sum = 0;
		$colArray = array_reverse(str_split($col));
		foreach ($colArray as $k => $v) {
			$sum += (ord($v)-64) * pow (26, $k);
		}
		return $sum;
	}

    /**
     * 错误打印
     * @param $msg
     */
    private function _throwException($msg)
    {
		echo "Error : {$msg}";exit;
	}

    /**
     * 防止克隆对象
     */
    private function __clone()
    {
		//防止clone函数克隆对象，破坏单例模式
	}

    /**
     * 析构函数
     */
    public function __destruct()
    {
		
	}


    /**
     * EXCEL文件类型验证
     * @param $file
     * @return bool
     */
    public function excelFileValidate($file)
    {
        if(!empty($file)){
            //验证上传文件类型
            $flag = 0;
            $file_array = explode ( ".", $file->name );
            $file_extension = strtolower ( array_pop ( $file_array ) );

            // 2.through the binary content to detect the file
            switch ($file_extension) {
                case "xls" :
                    // 2003 excel
                    $fh = fopen ( $file->tempName, "rb" );
                    $bin = fread ( $fh, 8 );
                    fclose ( $fh );
                    $strinfo = @unpack ( "C8chars", $bin );
                    $typecode = "";
                    foreach ( $strinfo as $num ) {
                        $typecode .= dechex ( $num );
                    }
                    if ($typecode == "d0cf11e0a1b11ae1") {
                        $flag = 1;
                    }
                    break;
                case "xlsx" :
                    // 2007 excel
                    $fh = fopen ( $file->tempName, "rb" );
                    $bin = fread ( $fh, 4 );
                    fclose ( $fh );
                    $strinfo = @unpack ( "C4chars", $bin );
                    $typecode = "";
                    foreach ( $strinfo as $num ) {
                        $typecode .= dechex ( $num );
                    }
                    if ($typecode == "504b34") {
                        $flag = 1;
                    }
                    break;
            }
            if($flag == 0){
                return false;
            } else {
                return true;
            }
        } else {
            return false;
        }
    }
}
?>